package com.telecom.cms.dao;

import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import java.util.HashMap;
import com.telecom.cms.dto.TwoId;
import com.telecom.cms.dto.CampCustInfoEx;
import com.telecom.cms.dto.CampCustInfo;
import com.telecom.cms.util.Constant;

public class CampCustInfoDAO {
	Dao dao = new DaoImp();

	String db = (new Constant()).getPara("DATABASE");

	public int insert(CampCustInfo transientInstance) {
		int rs = 0;
		String sql = "select * from  " + db
				+ ".Camp_Cust_Info where camp_id="
				+ transientInstance.getCampId()+" and camp_seq_id="
				+ transientInstance.getCampSeqId()+" and cust_id='"
				+ transientInstance.getCustId()+"' and prd_inst_id='"
				+ transientInstance.getPrdInstId()+"' and biz_atom_id="
				+ transientInstance.getBizAtomId();
		
		try {
			ResultSet rs2 = dao.select(sql);
			if (rs2.next())
				sql = "update " + db + ".Camp_Cust_Info set biz_atom_value='"
						+ transientInstance.getBizAtomValue() + "' where camp_id="
				        + transientInstance.getCampId()+" and camp_seq_id="
				        + transientInstance.getCampSeqId()+" and cust_id='"
				        + transientInstance.getCustId()+"' and prd_inst_id='"
				        + transientInstance.getPrdInstId()+"' and biz_atom_id="
				        + transientInstance.getBizAtomId();
			else
				sql = "insert into " + db + ".Camp_Cust_Info values('"
						+ transientInstance.getLatnId() + "' , '"
						+ transientInstance.getCampId() + "' , '"
						+ transientInstance.getCampSeqId() + "' , '"
						+ transientInstance.getCustId() + "' , '"
						+ transientInstance.getPrdInstId() + "' ,  "
						+ transientInstance.getShowAreaId() + "  , "
						+ transientInstance.getBizAtomId() + "  , '"
						+ transientInstance.getBizAtomValue() + "')";
			rs = dao.insert(sql);
			
			if (rs2 != null) {

				rs2.close();
			}

		} catch (Exception re) {
			System.out.println("save failed:" + re);
		}
		finally
		{
			dao.closeconn();
		}
		return rs;

	}

	public void delete(CampCustInfo persistentInstance) {

		String sql = "delete from " + db
				+ ".Camp_Cust_Info where camp_Id = "
				+ persistentInstance.getCampId();

		try {
			dao.delete(sql);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public void delete(String campid,String campseqid) {

		String sql = "delete from " + db
				+ ".Camp_Cust_Info where camp_Id = "
				+ campid+" and camp_seq_id="+campseqid;

		try {
			dao.delete(sql);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public CampCustInfo findById(String campid,String campseqid,String custid,String prdinstid,String bizatomid) {

		String sql = "select * from " + db
				+ ".Camp_Cust_Info where camp_id="
				        + campid+" and camp_seq_id="
				        + campseqid+" and cust_id='"
				        + custid+"' and prd_inst_id='"
				        + prdinstid+"' and biz_atom_id="
				        + bizatomid;
		CampCustInfo sd = new CampCustInfo();
		try {
			ResultSet rs = dao.select(sql);
			if (rs.next()) {

				sd.setLatnId(rs.getString(1));
				sd.setCampId(rs.getInt(2));
				sd.setCampSeqId(rs.getInt(3));

				sd.setCustId(rs.getString(4));
				sd.setPrdInstId(rs.getString(5));
				sd.setShowAreaId(rs.getInt(6));
				sd.setBizAtomId(rs.getInt(7));
				sd.setBizAtomValue(rs.getString(8));
			}
			if (rs != null) {

				rs.close();
			}

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
			dao.closeconn();
		}
		return sd;

	}

	public List findAllRaw(String campid,String campseqid,String custid,String prdinstid) {
		String sql = "select * from " + db
			+ ".Camp_Cust_Info where camp_id="
			+ campid+" and camp_seq_id="
			+ campseqid+" and cust_id='"
			+ custid+"' and prd_inst_id='"
			+ prdinstid+"'";
		List list = new ArrayList();
		try {
			ResultSet rs = dao.select(sql);
			while (rs.next()) {
				CampCustInfo sd = new CampCustInfo();
				sd.setLatnId(rs.getString(1));
				sd.setCampId(rs.getInt(2));
				sd.setCampSeqId(rs.getInt(3));
				sd.setCustId(rs.getString(4));
				sd.setPrdInstId(rs.getString(5));
				sd.setShowAreaId(rs.getInt(6));
				sd.setBizAtomId(rs.getInt(7));
				sd.setBizAtomValue(rs.getString(8));
				list.add(sd);
			}
			if (rs != null) {

				rs.close();
			}

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
			dao.closeconn();
		}
		return list;
	}
	
	public List findAll(String campid,String campseqid,String custid,String prdinstid,String billingcycleid) {
		String sql = "select a.camp_id,b.show_area_id,b.show_area_name,b.show_order as areaorder," 
				 +" a.biz_atom_id,d.biz_atom_name,c.show_order as segorder,a.biz_atom_value,a.billing_cycle_id "
                 +" from "+db+".Camp_Cust_Info a "
                 +" left join "+db+".camp_show_area  b on "
                 +" a.camp_id=b.camp_id and a.show_area_id=b.show_area_id "
                 +" left join "+db+".camp_seg  c on "
                 +" a.camp_id=c.camp_id and a.show_area_id=c.show_area_id and a.Biz_Atom_Id=c.Biz_Atom_Id "
                 +" left join "+db+".seg_biz_atom_def d on "
                 +" a.biz_atom_id=d.biz_atom_id where a.camp_id= "
			     + campid+" and a.camp_seq_id="
			     + campseqid+" and a.prd_inst_id='"
			     + prdinstid+"' and a.cust_id='"
			     + custid+"' and (a.billing_cycle_id like '"+billingcycleid+"' or a.billing_cycle_id='')   order by 4,2,7,5,9 desc";

		List list = new ArrayList();
		try {
			ResultSet rs = dao.select(sql);
			while (rs.next()) {
				CampCustInfoEx sd = new CampCustInfoEx();
				sd.setCustid(custid);
				sd.setPrdinstid(prdinstid);
				sd.setCampId(rs.getInt(1));
				sd.setShowAreaId(rs.getInt(2));
				sd.setShowAreaName(rs.getString(3));
				sd.setAreaOrder(rs.getInt(4));
				sd.setBizAtomId(rs.getInt(5));
				sd.setBizAtomName(rs.getString(6));
				sd.setSegOrder(rs.getInt(7));
				sd.setBizAtomValue(rs.getString(8).trim());
				sd.setBillingCycleId(rs.getString(9));
				list.add(sd);
			}
			if (rs != null) {

				rs.close();
			}

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
			dao.closeconn();
		}
		return list;
	}
	public List findAll() {
		String sql = "select * from " + db + ".Camp_Cust_Info";
		List list = new ArrayList();
		try {
			ResultSet rs = dao.select(sql);
			while (rs.next()) {
				CampCustInfo sd = new CampCustInfo();
				sd.setLatnId(rs.getString(1));
				sd.setCampId(rs.getInt(2));
				sd.setCampSeqId(rs.getInt(3));
				sd.setCustId(rs.getString(4));
				sd.setPrdInstId(rs.getString(5));
				sd.setShowAreaId(rs.getInt(6));
				sd.setBizAtomId(rs.getInt(7));
				sd.setBizAtomValue(rs.getString(8));
				list.add(sd);
			}
			if (rs != null) {

				rs.close();
			}

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
			dao.closeconn();
		}
		return list;
	}
	
	public List findAllCycle(String campid,String campseqid) {
		String sql = "select billing_cycle_id from " + db + ".Camp_Cust_Info where camp_id= "
			     + campid+" and camp_seq_id="
			     + campseqid+" and billing_cycle_id<>'' group by 1 order by 1 desc";
		List list = new ArrayList();
		try {
			ResultSet rs = dao.select(sql);
			while (rs.next()) {
				list.add(rs.getString(1));
			}
			if (rs != null) {

				rs.close();
			}

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
			dao.closeconn();
		}
		return list;
	}
	
	public HashMap findallmap(String campid,String campseqid,List allidlist,String billingcycleid,int from)
	{
	     HashMap result =new HashMap();
	     TwoId twoid=null;
	     TwoId twoid2=null;
	     int preread=Integer.parseInt(com.telecom.cms.util.Constant.getStPara("CONT_PRE_READ"));
	     for (int i=from;i<allidlist.size()&&i<preread+from;i++)
	     {
	    	 twoid=(TwoId)allidlist.get(i);
	    	 twoid2=new TwoId();
	    	 twoid2.setCustId(twoid.getCustId());
	    	 twoid2.setPrdInstId(twoid.getPrdInstId());
	    	 twoid2.setAccsnbr(twoid.getAccsnbr());
	    	 twoid2.setBillingcycleid(billingcycleid);
	    	 result.put(twoid2, findAll(campid,campseqid,twoid.getCustId(),twoid.getPrdInstId(),billingcycleid));
	     }
	     return result;
	}

}
